// 校友宝数据库完整结构初始化脚本
const mysql = require('mysql2/promise');
const dotenv = require('dotenv');
const path = require('path');
const fs = require('fs');

// 尝试加载.env.local文件
const envPath = path.resolve(process.cwd(), '.env.local');
dotenv.config({ path: envPath });

// 如果环境变量未加载成功，尝试其他路径
if (!process.env.DB_HOST) {
  const parentEnvPath = path.resolve(process.cwd(), '..', '.env.local');
  if (fs.existsSync(parentEnvPath)) {
    dotenv.config({ path: parentEnvPath });
    console.log(`已从上级目录加载环境变量: ${parentEnvPath}`);
  }
}

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'rm-bp14wd51i709c3z79ho.mysql.rds.aliyuncs.com',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'Ircnjfdhnvxwz@123',
  port: process.env.DB_PORT || 3306,
  multipleStatements: true, // 允许执行多条SQL语句
  connectTimeout: 15000, // 15秒连接超时
};

console.log('数据库连接信息:');
console.log(`主机: ${dbConfig.host}`);
console.log(`用户: ${dbConfig.user}`);
console.log(`端口: ${dbConfig.port}`);

// 完整的数据库结构SQL
const createFullSchemaSQL = `
-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS \`${process.env.DB_NAME || 'xiaoyoubao'}\`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE \`${process.env.DB_NAME || 'xiaoyoubao'}\`;

-- 创建用户表
CREATE TABLE IF NOT EXISTS \`users\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`username\` VARCHAR(50) NOT NULL UNIQUE,
  \`password\` VARCHAR(255) NOT NULL,
  \`email\` VARCHAR(100) NOT NULL UNIQUE,
  \`avatar\` VARCHAR(255),
  \`bio\` TEXT,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS \`idx_username\` ON \`users\`(\`username\`);
CREATE INDEX IF NOT EXISTS \`idx_email\` ON \`users\`(\`email\`);

-- 创建用户详细信息表
CREATE TABLE IF NOT EXISTS \`user_profiles\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`real_name\` VARCHAR(50),
  \`gender\` ENUM('male', 'female', 'other') DEFAULT 'other',
  \`birth_date\` DATE,
  \`phone\` VARCHAR(20),
  \`address\` VARCHAR(255),
  \`city\` VARCHAR(50),
  \`province\` VARCHAR(50),
  \`country\` VARCHAR(50) DEFAULT 'China',
  \`postal_code\` VARCHAR(20),
  \`personal_website\` VARCHAR(255),
  \`social_links\` JSON,
  \`is_verified\` BOOLEAN DEFAULT FALSE,
  \`verification_type\` VARCHAR(50),
  \`verification_time\` DATETIME,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_user_id_profiles\` ON \`user_profiles\`(\`user_id\`);

-- 创建教育经历表
CREATE TABLE IF NOT EXISTS \`educations\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`school_name\` VARCHAR(100) NOT NULL,
  \`degree\` VARCHAR(50),
  \`field_of_study\` VARCHAR(100),
  \`start_date\` DATE,
  \`end_date\` DATE,
  \`is_current\` BOOLEAN DEFAULT FALSE,
  \`description\` TEXT,
  \`activities\` TEXT,
  \`achievements\` TEXT,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_user_id_educations\` ON \`educations\`(\`user_id\`);
CREATE INDEX IF NOT EXISTS \`idx_school_name\` ON \`educations\`(\`school_name\`);

-- 创建工作经历表
CREATE TABLE IF NOT EXISTS \`work_experiences\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`company_name\` VARCHAR(100) NOT NULL,
  \`position\` VARCHAR(100) NOT NULL,
  \`location\` VARCHAR(100),
  \`start_date\` DATE,
  \`end_date\` DATE,
  \`is_current\` BOOLEAN DEFAULT FALSE,
  \`description\` TEXT,
  \`responsibilities\` TEXT,
  \`achievements\` TEXT,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_user_id_work\` ON \`work_experiences\`(\`user_id\`);
CREATE INDEX IF NOT EXISTS \`idx_company_name\` ON \`work_experiences\`(\`company_name\`);

-- 创建技能表
CREATE TABLE IF NOT EXISTS \`skills\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`skill_name\` VARCHAR(100) NOT NULL,
  \`proficiency\` ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'intermediate',
  \`years_of_experience\` INT,
  \`is_highlighted\` BOOLEAN DEFAULT FALSE,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_user_id_skills\` ON \`skills\`(\`user_id\`);
CREATE INDEX IF NOT EXISTS \`idx_skill_name\` ON \`skills\`(\`skill_name\`);

-- 创建公司表
CREATE TABLE IF NOT EXISTS \`companies\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`name\` VARCHAR(100) NOT NULL,
  \`description\` TEXT,
  \`logo\` VARCHAR(255),
  \`website\` VARCHAR(255),
  \`industry\` VARCHAR(100),
  \`size\` VARCHAR(50),
  \`founded_year\` INT,
  \`headquarters\` VARCHAR(100),
  \`is_verified\` BOOLEAN DEFAULT FALSE,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS \`idx_company_name\` ON \`companies\`(\`name\`);
CREATE INDEX IF NOT EXISTS \`idx_company_industry\` ON \`companies\`(\`industry\`);

-- 创建职位信息表
CREATE TABLE IF NOT EXISTS \`job_posts\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`company_id\` INT NOT NULL,
  \`title\` VARCHAR(100) NOT NULL,
  \`description\` TEXT NOT NULL,
  \`requirements\` TEXT,
  \`location\` VARCHAR(100),
  \`salary_range\` VARCHAR(100),
  \`employment_type\` ENUM('full-time', 'part-time', 'contract', 'internship') DEFAULT 'full-time',
  \`is_remote\` BOOLEAN DEFAULT FALSE,
  \`application_deadline\` DATE,
  \`status\` ENUM('open', 'closed', 'draft') DEFAULT 'open',
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`company_id\`) REFERENCES \`companies\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_job_company_id\` ON \`job_posts\`(\`company_id\`);
CREATE INDEX IF NOT EXISTS \`idx_job_title\` ON \`job_posts\`(\`title\`);
CREATE INDEX IF NOT EXISTS \`idx_job_location\` ON \`job_posts\`(\`location\`);
CREATE INDEX IF NOT EXISTS \`idx_job_status\` ON \`job_posts\`(\`status\`);

-- 创建校友帖子表
CREATE TABLE IF NOT EXISTS \`alumni_posts\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`title\` VARCHAR(200) NOT NULL,
  \`content\` TEXT NOT NULL,
  \`post_type\` ENUM('general', 'event', 'job', 'question', 'news') DEFAULT 'general',
  \`view_count\` INT DEFAULT 0,
  \`like_count\` INT DEFAULT 0,
  \`is_featured\` BOOLEAN DEFAULT FALSE,
  \`status\` ENUM('draft', 'published', 'archived') DEFAULT 'published',
  \`tags\` JSON,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_post_user_id\` ON \`alumni_posts\`(\`user_id\`);
CREATE INDEX IF NOT EXISTS \`idx_post_type\` ON \`alumni_posts\`(\`post_type\`);
CREATE INDEX IF NOT EXISTS \`idx_post_status\` ON \`alumni_posts\`(\`status\`);

-- 创建帖子评论表
CREATE TABLE IF NOT EXISTS \`post_comments\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`post_id\` INT NOT NULL,
  \`user_id\` INT NOT NULL,
  \`content\` TEXT NOT NULL,
  \`parent_id\` INT DEFAULT NULL,
  \`like_count\` INT DEFAULT 0,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`post_id\`) REFERENCES \`alumni_posts\`(\`id\`) ON DELETE CASCADE,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE,
  FOREIGN KEY (\`parent_id\`) REFERENCES \`post_comments\`(\`id\`) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS \`idx_comment_post_id\` ON \`post_comments\`(\`post_id\`);
CREATE INDEX IF NOT EXISTS \`idx_comment_user_id\` ON \`post_comments\`(\`user_id\`);
CREATE INDEX IF NOT EXISTS \`idx_comment_parent_id\` ON \`post_comments\`(\`parent_id\`);

-- 创建AI聊天会话表
CREATE TABLE IF NOT EXISTS \`ai_chat_sessions\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`user_id\` INT NOT NULL,
  \`title\` VARCHAR(100),
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  \`updated_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_ai_chat_user_id\` ON \`ai_chat_sessions\`(\`user_id\`);

-- 创建AI聊天消息表
CREATE TABLE IF NOT EXISTS \`ai_chat_messages\` (
  \`id\` INT AUTO_INCREMENT PRIMARY KEY,
  \`session_id\` INT NOT NULL,
  \`role\` ENUM('user', 'assistant', 'system') NOT NULL,
  \`content\` TEXT NOT NULL,
  \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (\`session_id\`) REFERENCES \`ai_chat_sessions\`(\`id\`) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS \`idx_message_session_id\` ON \`ai_chat_messages\`(\`session_id\`);
CREATE INDEX IF NOT EXISTS \`idx_message_role\` ON \`ai_chat_messages\`(\`role\`);
`;

async function main() {
  let connection;

  try {
    // 连接到阿里云数据库服务器（不选择具体数据库）
    console.log('正在连接到阿里云数据库...');
    connection = await mysql.createConnection(dbConfig);

    // 创建和初始化数据库结构
    console.log('正在创建完整数据库结构...');
    await connection.query(createFullSchemaSQL);

    console.log('数据库结构创建完成！');

    // 验证表创建结果
    const dbName = process.env.DB_NAME || 'xiaoyoubao';
    const [tables] = await connection.query(`
      SELECT table_name
      FROM information_schema.tables
      WHERE table_schema = ?
      ORDER BY table_name
    `, [dbName]);

    console.log(`\n成功创建的表 (${tables.length}个):`)
    tables.forEach((row, index) => {
      console.log(`${index + 1}. ${row.table_name}`);
    });

  } catch (error) {
    console.error('创建数据库结构时发生错误:', error.message);
    console.log('\n请检查以下可能的问题:');
    console.log('1. 确认数据库服务器地址和端口是否正确');
    console.log('2. 确认用户名和密码是否正确');
    console.log('3. 检查网络连接是否正常');
    console.log('4. 检查SQL语法是否正确');
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n数据库连接已关闭');
    }
  }
}

main().catch(err => {
  console.error('执行过程中发生未捕获的错误:', err);
  process.exit(1);
});